if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateReviewInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateReviewInfo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************        
* Name:   [UpdateReviewInfo]        
* Purpose:  Updates the review date and status to 'Reviewed' for the passed in COIIds(note: only with status 'Submitted')      
        
 The COIIDs is contained in the XML which has the following form:        
  <NewDataSet>        
   <Table>        
    <COIId>23471</COIId>        
    </Table>        
   <Table>        
    <COIId>19184</COIId>        
   </Table>        
   ...        
  </NewDataSet>        
        
If the XML contains additional elements as siblings of the COIId element, they will be ignored.        
If the XML contains siblings of the Table element, they will be ignored.        
The COI IDs from the XML will be loaded into a table variable so that an inner join may        
be done with that table to limit the resultset to the specified COIs.        
        
Note that the table variable is sequenced in the same order as the COI IDs are specified in the        
XML. This preserves any sorting sequence that had already been applied to the COI.        
*        
* PARAMETERS(IN)        
* Name          Description             
* -------------      -------------------------------------------        
* @xml       xml Document containing list of COIIds(REQUIRED)          
*********************************************************************/        
        
CREATE PROCEDURE [dbo].[UpdateReviewInfo]        
(        
@xml text,  
@reviewerPersonId int,
@skipReviewerCOI bit = 1        
)        
AS BEGIN        
SET NOCOUNT ON        
        
 declare @xmlDoc int          
 declare @COIIds table (Sequence int identity, [COIId] int)          
 exec sp_xml_preparedocument @xmlDoc output, @xml          
 insert into @COIIds select [COIId] from openxml(@xmlDoc, '//IdList', 2) with ([COIId] int)          
 exec sp_xml_removedocument @xmlDoc      
      
Declare @currentDate DateTime      
set @currentDate = GetDate()      
        
Update ConflictOfInterest SET       
  DateReviewed=@currentDate,      
  Status='COISTATUS_REVIEWED',  
  ReviewerPersonId = @reviewerPersonId      
Where      
  Id in(Select coi.[Id] from ConflictOfInterest coi       
		Inner Join @COIIds tempTable      
			on coi.[Id]=tempTable.[COIId]      
		Where coi.Status='COISTATUS_SUBMITTED'
		and coi.AdmPersonId <> case @skipReviewerCOI
									when 1 then @reviewerPersonId
									when 0 then -1
							   end
	   )           
END 